package com.conorroche.taskmanagement;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {
	public DBAdapter(Context ctx){
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}
	private static class DatabaseHelper extends SQLiteOpenHelper{
		DatabaseHelper(Context context){
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		@Override
		public void onCreate(SQLiteDatabase db){
			try{
				db.execSQL(DATABASE_CREATE1);
				db.execSQL(DATABASE_CREATE2);
				db.execSQL(DATABASE_CREATE3);
				db.execSQL(DATABASE_CREATE4);
				db.execSQL(DATABASE_CREATE5);
				db.execSQL(DATABASE_CREATE6);
				db.execSQL(DATABASE_CREATE7);
				db.execSQL(DATABASE_CREATE8);
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
			db.execSQL("DROP TABLE IF EXISTS tasks");
			db.execSQL("DROP TABLE IF EXISTS tags");
			onCreate(db);
		}
	}
	public DBAdapter open() throws SQLException{
		db = DBHelper.getWritableDatabase();
		return this;
	}
	public void close(){
		DBHelper.close();
	}
	public long insertTask(String name, String location, String importance, String time, String date,// String friends, 
				String notes, boolean locationRemind, boolean dateRemind, String remindDelay, boolean complete, double longitude, double latitude){
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TASKNAME, name);
		initialValues.put(KEY_TASKLOCATION, location);
		initialValues.put(KEY_TASKIMPORTANCE, importance);
		initialValues.put(KEY_TASKTIME, time);
		initialValues.put(KEY_TASKDATE, date);
//		initialValues.put(KEY_TASKFRIENDS, friends);
		initialValues.put(KEY_TASKNOTES, notes);
		initialValues.put(KEY_TASKLOCATIONREMINDER, locationRemind ? 1 : 0);
		initialValues.put(KEY_TASKDATEREMINDER, dateRemind ? 1 : 0);
		initialValues.put(KEY_TASKCOMPLETE, complete ? 1 : 0);
		initialValues.put(KEY_TASKLONGITUDE, longitude);
		initialValues.put(KEY_TASKLATITUDE, latitude);
		return db.insert("tasks", null, initialValues);
	}
	public boolean deleteTask(long rowId){
		return db.delete("tasks", KEY_ROWID +"=" + rowId, null) > 0;
	}
	public boolean deleteTag(long rowId){
		return db.delete("tags", KEY_ROWID + "=" + rowId, null) > 0;
	}
	public Cursor getAllTasks(){
		return db.rawQuery("select * from tasks where complete = 0;", null);
	}
	public Cursor getTaskById(long rowId) throws SQLException{
		Cursor mCursor = db.query("tasks", new String[] {KEY_ROWID, KEY_TASKNAME, KEY_TASKLOCATION, KEY_TASKIMPORTANCE, KEY_TASKTIME, KEY_TASKDATE, 
				KEY_TASKDATE, 
				//KEY_TASKFRIENDS, 
				KEY_TASKNOTES, KEY_TASKLOCATIONREMINDER, KEY_TASKDATEREMINDER, KEY_TASKREMINDDELAY, KEY_TASKCOMPLETE, KEY_TASKLONGITUDE, KEY_TASKLATITUDE},
				KEY_ROWID + "=" + rowId, null, null, null, null);
		if(mCursor != null){
			mCursor.moveToFirst();
		}
		
		return mCursor;
	}
	public Cursor getTaskByName(String name){
		Cursor mCursor = db.rawQuery("select * from tasks where name = '" + name + "';", null);
		if(mCursor != null){
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	public long updateTask(long rowId, String name, String location, String importance, String time, String date, 
			//String friends, 
			String notes, boolean locationRemind, boolean dateRemind, String remindDelay, boolean complete, double longitude, double latitude){
		ContentValues args = new ContentValues();
		args.put(KEY_TASKNAME, name);
		args.put(KEY_TASKLOCATION, location);
		args.put(KEY_TASKIMPORTANCE, importance);
		args.put(KEY_TASKTIME, time);
		args.put(KEY_TASKDATE, date);
//		args.put(KEY_TASKFRIENDS, friends);
		args.put(KEY_TASKNOTES, notes);
		args.put(KEY_TASKLOCATIONREMINDER, locationRemind ? 1 : 0);
		args.put(KEY_TASKDATEREMINDER, dateRemind ? 1 : 0);
		args.put(KEY_TASKCOMPLETE, complete ? 1 : 0);
		args.put(KEY_TASKLONGITUDE, longitude);
		args.put(KEY_TASKLATITUDE, latitude);
		return db.update("tasks", args, KEY_ROWID + " = " + rowId, null);
	}
	
	public void markComplete(long id){
		db.rawQuery("update tasks set complete = 1 where _id = '" + id + "';", null);	
	}
	public Cursor getAllCompleteTasks(){
		Cursor mCursor = db.rawQuery("select * from tasks were complete = 1;", null);
		
		if(mCursor != null){
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	public long insertTag(String tagName, String colour, int rank){
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TAGNAME, tagName);
		initialValues.put(KEY_TAGCOLOUR, colour);
		initialValues.put(KEY_TAGRANK, rank);
		return db.insert("tags", null, initialValues);
	}
	public Cursor getTaggedTasks(String tag){
		Cursor mCursor = db.rawQuery("select * from tasks where importance = '" + tag+ "';", null);
		if(mCursor != null){
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	//get all tags
	public Cursor getAllTags(){
		return db.query("tags", new String[]{KEY_TAGNAME, KEY_TAGCOLOUR, KEY_TAGRANK}, null, null, null, null, null);
	}
	
	//---sets all the row id's for the tasks table
	public static final String KEY_ROWID = "_id";
	public static final String KEY_TASKNAME = "name";
	public static final String KEY_TASKLOCATION = "location";
	public static final String KEY_TASKIMPORTANCE = "importance";
	public static final String KEY_TASKTIME = "time";
	public static final String KEY_TASKDATE = "date";
//	public static final String KEY_TASKFRIENDS = "friends";
	public static final String KEY_TASKNOTES = "notes";
	public static final String KEY_TASKLOCATIONREMINDER = "locationRemind";
	public static final String KEY_TASKDATEREMINDER = "dateRemind";
	public static final String KEY_TASKREMINDDELAY = "remindDelay";
	public static final String KEY_TASKCOMPLETE = "complete";
	public static final String KEY_TASKLONGITUDE = "longitude";
	public static final String KEY_TASKLATITUDE = "latitude";
	public static final String KEY_TAGNAME = "name";
	public static final String KEY_TAGCOLOUR = "colour";
	public static final String KEY_TAGRANK = "rank";
	public static final String TAG = "DBAdapter";
	private static final String DATABASE_NAME = "TaskManagementDB";
	private static final int DATABASE_VERSION = 17;

	private static final String DATABASE_CREATE1 = "CREATE TABLE tasks (_id INTEGER PRIMARY KEY, name TEXT, location TEXT, importance TEXT, " +
													"time TEXT, date TEXT, notes TEXT, locationRemind NUMERIC, dateRemind NUMERIC, remindDelay TEXT, " +
													"complete NUMERIC, longitude NUMERIC, latitude NUMERIC);";
	private static final String DATABASE_CREATE2 = "CREATE TABLE tags (_id INTEGER PRIMARY KEY, name TEXT, colour TEXT, rank NUMERIC);";
	private static final String DATABASE_CREATE3 = "INSERT INTO tags VALUES(1,'Very Important','red',1);";
	private static final String DATABASE_CREATE4 = "INSERT INTO tags VALUES(2,'Important','orange',2);";
	private static final String DATABASE_CREATE5 = "INSERT INTO tags VALUES(3,'College','yellow',3);";
	private static final String DATABASE_CREATE6 = "INSERT INTO tags VALUES(4,'Not Important','Blue',10);";
	private static final String DATABASE_CREATE7 = "INSERT INTO tags VALUES(5, 'Home', 'Green', 5);";
	private static final String DATABASE_CREATE8 = "INSERT INTO tags VALUES(6, 'Complete', 'Green', 5);";
	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
}
